<?php
/**
 * User: lukas
 * Date: 12/5/12
 * Time: 10:30 AM
 */
class DatabaseTools
{
    private $connection;
    private $user;

    public function login()
    {


        $md5Password = md5($this->user->getPassword()); // TODO ggf. ändern da es nicht als sicher gilt
        $query = "SELECT * from " . DataBaseStructure::$TABLE_USER . " where " . DataBaseStructure::$USER_COLUMN_EMAIL . " =  " . $this->user->getEmail() . " AND " . DataBaseStructure::$USER_COLUMN_PASSWORD . " = " . $md5Password;

        $result = mysql_query($query);
        if ($result) {
            if (mysql_num_rows($result) != 0) {
                $row = mysql_fetch_assoc($result);

                $this->user->setName([DataBaseStructure::$USER_COLUMN_ID]);
                $this->user->setId($row[DataBaseStructure::$USER_COLUMN_NAME]);

                // TODO auf projects und tasks erweitern
            } else {
                // mehr als eine Zeile zurückbekommen
            }
        } else {
            // DB Fehler
        }
    }

    public function getProjects()
    { // TODO QUERY auf Id des Benutzers erweitern

        $query = "SELECT * from " . DataBaseStructure::$TABLE_PROJECT . "WHERE " . DataBaseStructure::$PROJECT_COLUMN_ID .
            " = (SELECT " . DataBaseStructure::$USER2PROJECT_COLUMN_PROJECT_ID . " from " . DataBaseStructure::$TABLE_USER2PROJECT .
            " WHERE " . DataBaseStructure::$USER2PROJECT_COLUMN_USER_ID . " = " . $this->user->getId() . ")";
        $result = mysql_query($query);

        $projects = array();
        if ($result) {
            while ($row = mysql_fetch_assoc($result)) {
                $project = new Project($row[DataBaseStructure::$PROJECT_COLUMN_ID], $row[DataBaseStructure::$PROJECT_COLUMN_NAME]);
                // TODO andere Spalten auch einfügen

                $projects[count($projects)] = $project;
            }
        }

        return $projects;

        // return projects als array
    }

    public function getProjectNames()
    {

        $query = "SELECT " . DataBaseStructure::$PROJECT_COLUMN_NAME . " from " . DataBaseStructure::$TABLE_PROJECT;

        $result = mysql_query($query);
        $names = array();
        if ($result) {
            while ($row = mysql_fetch_assoc($result)) {
                $name = $row[DataBaseStructure::$PROJECT_COLUMN_NAME];
                $names[count($names)] = $name;
            }

            return $names;
        } else {
            // DB Fehler
            return null;
        }
    }

    public function createUser($name)
    {
        $query = "INSERT INTO ".DataBaseStructure::$TABLE_USER. "(".DataBaseStructure::$USER_COLUMN_EMAIL.", ".DataBaseStructure::$USER_COLUMN_NAME.", ".DataBaseStructure::$USER_COLUMN_PASSWORD.") VALUES (".$this->user.getEmail
    }

    public function updateUser($user)
    {
        if (is_a($user, "User")) {
            $query = "UPDATE " . DataBaseStructure::$TABLE_USER . " SET " . DataBaseStructure::$USER_COLUMN_EMAIL . "=" .
                $user->getEmail() . ", " . DataBaseStructure::$USER_COLUMN_NAME . "=" . $user->getName() . ", " .
                DataBaseStructure::$USER_COLUMN_PASSWORD . "=" . md5($user->getPassword()) . " WHERE " .
                DataBaseStructure::$USER_COLUMN_ID . " = " . $user->getId();
            $result = mysql_query($query);

        } else {
            // falscher Datentyp
        }
    }

    public function addThisUserToProject($project)
    {
        if (is_a($project, "Project")) {
            $query = "INSERT INTO " . DataBaseStructure::$TABLE_USER2PROJECT . "(" . DataBaseStructure::$USER2PROJECT_COLUMN_USER_ID .
                ", " . DataBaseStructure::$USER2PROJECT_COLUMN_PROJECT_ID . ") VALUES (" . $this->user->getId() . ", " .
                $project->getId() . ")";
            $result = mysql_query($query);
        }
    }

    public function addUserToProject($user, $project)
    {
        if (is_a($user, "User") and is_a($project, "Project")) {
            $query = "INSERT INTO " . DataBaseStructure::$TABLE_USER2PROJECT . "(" . DataBaseStructure::$USER2PROJECT_COLUMN_USER_ID . ", " . DataBaseStructure::$USER2PROJECT_COLUMN_PROJECT_ID . ") VALUES (" . $user->getId() . ", " . $project->getId() . ")";
            $result = mysql_query($query);

        }
    }


    public function setConnection($connection)
    {
        $this->connection = $connection;
    }

    public function getConnection()
    {
        return $this->connection;
    }

    public function setUser($user)
    {
        $this->user = $user;
    }

    public function getUser()
    {
        return $this->user;
    }


}
